package cn.itcast.itcaststore.dao.impl;

import cn.itcast.itcaststore.dao.OrderDao;
import cn.itcast.itcaststore.domain.OrderItem;
import cn.itcast.itcaststore.domain.Orders;
import cn.itcast.itcaststore.domain.Products;
import cn.itcast.itcaststore.domain.User;
import cn.itcast.itcaststore.utils.DataSourceUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao {
    private QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

    @Override
    public void addOrderItem(Orders order) throws SQLException {
        //创建sql语句
        String sql = "insert into orderItem values(?,?,?)";
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner();
        List<OrderItem> items = order.getOrderItems();
        Object[][] params = new Object[items.size()][3];
        for (int i = 0; i < params.length; i++) {
            params[i][0] = items.get(i).getOrder().getId();
            params[i][1] = items.get(i).getP().getId();
            params[i][2] = items.get(i).getBuynum();
        }
        runner.batch(DataSourceUtils.getConnection(), sql, params);
    }

    @Override
    public List<Orders> findAllOrder() throws SQLException {
        //创建查询sql
        String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id order by orders.user_id";
        return runner.query(sql, new ResultSetHandler<List<Orders>>() {
            public List<Orders> handle(ResultSet rs) throws SQLException {
                //创建订单集合
                ArrayList<Orders> orders = new ArrayList<Orders>();
                while (rs.next()) {
                    Orders order = new Orders();
                    order.setId(rs.getString("orders.id"));
                    order.setMoney(rs.getDouble("orders.money"));
                    order.setOrderTime(rs.getDate("orders.ordertime"));//****************
                    order.setPayState(rs.getInt("orders.paystate"));
                    order.setReceiverAddress(rs.getString("orders.receiverAddress"));
                    order.setReceiverName(rs.getString("orders.receiverName"));
                    order.setReceiverPhone(rs.getString("orders.receiverPhone"));
                    orders.add(order);
                    User user = new User();
                    user.setId(rs.getInt("user.id"));
                    user.setEmail(rs.getString("user.email"));
                    user.setGender(rs.getString("user.gender"));
                    user.setActiveCode(rs.getString("user.activecode"));
                    user.setIntroduce(rs.getString("user.introduce"));
                    user.setPassword(rs.getString("user.password"));
                    user.setRegistTime(rs.getDate("user.registtime"));
                    user.setRole(rs.getString("user.role"));
                    user.setState(rs.getInt("user.state"));
                    user.setTelephone(rs.getString("user.telephone"));
                    user.setUsername(rs.getString("user.username"));
                    order.setUser(user);
                }
                return orders;
            }
        });
    }

    @Override
    public List<Orders> findOrderByManyCondition(String orderId, String receiverName) throws SQLException {
        //1.创建集合对象
        ArrayList<Object> objs = new ArrayList<Object>();
        //2.定义查询sql
        String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id";
        //3.根据参数拼接sql语句
        if (orderId != null && orderId.trim().length() > 0) {
            sql += " and orders.id=?";
            objs.add(orderId);
        }
        if (receiverName != null && receiverName.trim().length() > 0) {
            sql += " and receiverName=?";
            objs.add(receiverName);
        }
        sql += " order by orders.user_id";
        //4.返回QueryRunner对象query方法的执行结果
        return runner.query(sql, new ResultSetHandler<List<Orders>>() {
            public List<Orders> handle(ResultSet rs) throws SQLException {
                //创建订单集合
                ArrayList<Orders> orders = new ArrayList<>();
                while (rs.next()) {
                    Orders order = new Orders();
                    order.setId(rs.getString("orders.id"));
                    order.setMoney(rs.getDouble("orders.money"));
                    order.setOrderTime(rs.getDate("orders.ordertime"));//****************
                    order.setPayState(rs.getInt("orders.paystate"));
                    order.setReceiverAddress(rs.getString("orders.receiverAddress"));
                    order.setReceiverName(rs.getString("orders.receiverName"));
                    order.setReceiverPhone(rs.getString("orders.receiverPhone"));
                    orders.add(order);
                    User user = new User();
                    user.setId(rs.getInt("user.id"));
                    user.setEmail(rs.getString("user.email"));
                    user.setGender(rs.getString("user.gender"));
                    user.setActiveCode(rs.getString("user.activecode"));
                    user.setIntroduce(rs.getString("user.introduce"));
                    user.setPassword(rs.getString("user.password"));
                    user.setRegistTime(rs.getDate("user.registtime"));
                    user.setRole(rs.getString("user.role"));
                    user.setState(rs.getInt("user.state"));
                    user.setTelephone(rs.getString("user.telephone"));
                    user.setUsername(rs.getString("user.username"));
                    order.setUser(user);
                }
                return orders;
            }
        }, objs.toArray());
    }

    @Override
    public Orders findOrderById(String orderId) throws SQLException {
        //定义查询sql
        String sql = " select * from orders,user where orders.user_id=user.id and orders.id=?";
        //返回QueryRunner对象query方法的执行结果
        return runner.query(sql, new ResultSetHandler<Orders>() {
            public Orders handle(ResultSet rs) throws SQLException {
                //创建订单
                Orders order = new Orders();
                while (rs.next()) {
                    order.setId(rs.getString("orders.id"));
                    order.setMoney(rs.getDouble("orders.money"));
                    order.setOrderTime(rs.getDate("orders.ordertime"));//****************
                    order.setPayState(rs.getInt("orders.paystate"));
                    order.setReceiverAddress(rs.getString("orders.receiverAddress"));
                    order.setReceiverName(rs.getString("orders.receiverName"));
                    order.setReceiverPhone(rs.getString("orders.receiverPhone"));
                    User user = new User();
                    user.setId(rs.getInt("user.id"));
                    user.setEmail(rs.getString("user.email"));
                    user.setGender(rs.getString("user.gender"));
                    user.setActiveCode(rs.getString("user.activecode"));
                    user.setIntroduce(rs.getString("user.introduce"));
                    user.setPassword(rs.getString("user.password"));
                    user.setRegistTime(rs.getDate("user.registtime"));
                    user.setRole(rs.getString("user.role"));
                    user.setState(rs.getInt("user.state"));
                    user.setTelephone(rs.getString("user.telephone"));
                    user.setUsername(rs.getString("user.username"));
                    order.setUser(user);
                }
                return order;
            }
        }, orderId);
    }

    @Override
    public List<OrderItem> findOrderItemByOrder(final Orders order) throws SQLException {
        //定义查询sql
        String sql = "select * from orderItem,products where products.id=orderItem.product_id and order_id=? ";
        //返回QueryRunner对象query方法的执行结果
        return runner.query(sql, new ResultSetHandler<List<OrderItem>>() {
            public List<OrderItem> handle(ResultSet rs) throws SQLException {
                //创建订单集合
                ArrayList<OrderItem> items = new ArrayList<OrderItem>();
                while (rs.next()) {
                    OrderItem item = new OrderItem();
                    item.setOrder(order);
                    item.setBuynum(rs.getInt("buynum"));
                    Products p = new Products();
                    p.setCategory(rs.getString("category"));
                    p.setId(rs.getString("id"));
                    p.setDescription(rs.getString("description"));
                    p.setImgUrl(rs.getString("imgurl"));
                    p.setName(rs.getString("name"));
                    p.setpNum(rs.getInt("pnum"));
                    p.setPrice(rs.getDouble("price"));
                    item.setP(p);
                    items.add(item);
                }
                return items;
            }
        }, order.getId());
    }

    @Override
    public void delOrderItems(String orderId) throws SQLException {
        //创建sql语句
        String sql = "delete from orderItem where order_id = ?";
        //调用方法
        runner.update(sql,orderId);
    }

    @Override
    public void delOrderById(String orderId) throws SQLException {
          //创建sql语句
          String sql = "delete from orders where id = ?";
          //调用方法
          runner.update(sql,orderId);
    }

    @Override
    public void addOrder(Orders order) throws SQLException {
        //创建Sql语句
        String sql = "insert into orders values(?,?,?,?,?,0,null,?)";
        //生成执行sql语句的QueryRunner
        QueryRunner runner = new QueryRunner();
        //执行update()方法
        runner.update(DataSourceUtils.getConnection(), sql, order.getId(), order.getMoney(), order.getReceiverAddress(), order.getReceiverName(), order.getReceiverPhone(), order.getUser().getId());
    }

    @Override
    public List<Orders> findOrderByUser(User user) throws SQLException {
        String sql = "select * from orders where user_id=?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new ResultSetHandler<List<Orders>>() {
            public List<Orders> handle(ResultSet rs) throws SQLException {//创建一个空的结果集rs
                List<Orders> orders = new ArrayList<Orders>();
                while (rs.next()) {
                    Orders order = new Orders();
                    order.setId(rs.getString("id"));
                    order.setMoney(rs.getDouble("money"));
                    order.setOrderTime(rs.getDate("ordertime"));
                    order.setPayState(rs.getInt("paystate"));
                    order.setReceiverAddress(rs.getString("receiverAddress"));
                    order.setReceiverName(rs.getString("receiverName"));
                    order.setReceiverPhone(rs.getString("receiverPhone"));
                    order.setUser(user);
                    orders.add(order);
                }
                return orders;//返回一个order集合
            }
        }, user.getId());//与sql语句进行拼接
    }

    @Override
    public void updateOrderState(String id) throws SQLException {
        String sql = "update orders set paystate=1 where id=?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        runner.update(sql, id);
    }


}

